﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Text;
using Entities;

namespace Distribution.Data
{
    public class DMeter
    {
        #region - Public methods -

        public static DataTable GetMeterDetails(EPole pole)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da;
            StringBuilder query = new StringBuilder();

            try
            {
                _Con = Helper.GetConnection();

                query.Append("SELECT tb_Meters.MeterId, tb_Meters.Number ");
                query.Append("FROM tb_Meters ");
                query.Append("WHERE PoleFk = @PoleId ");
                query.Append("ORDER BY tb_Meters.MeterId");

                _Da = new SqlCeDataAdapter(query.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                _Da.Fill(_Dt);
                return _Dt;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static EMeter GetById(int id)
        {
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();

                query.Append("SELECT MeterId, Latitude, Longitude, Number, ImageFileName, Comments, PoleFk ");
                query.Append("FROM tb_Meters ");
                query.Append("WHERE MeterId = @MeterId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, id));

                SqlCeDataReader dr = command.ExecuteReader();
                EMeter loadedMeter = new EMeter();

                if (dr.Read())
                {
                    loadedMeter.Id = dr.GetInt32(dr.GetOrdinal("MeterId"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Latitude")))
                        loadedMeter.Latitude = dr.GetDouble(dr.GetOrdinal("Latitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Longitude")))
                        loadedMeter.Longitude = dr.GetDouble(dr.GetOrdinal("Longitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Number")))
                        loadedMeter.Number = dr.GetString(dr.GetOrdinal("Number"));

                    if (!dr.IsDBNull(dr.GetOrdinal("ImageFileName")))
                    {
                        loadedMeter.Image = new EImage(dr.GetString(dr.GetOrdinal("ImageFileName")));
                        loadedMeter.Image.ImageType = ImageType.Meter;
                    }

                    if (!dr.IsDBNull(dr.GetOrdinal("PoleFk")))
                    {
                        loadedMeter.Pole = new EPole();
                        loadedMeter.Pole.Id = dr.GetInt32(dr.GetOrdinal("PoleFk"));
                    }

                    if (!dr.IsDBNull(dr.GetOrdinal("Comments")))
                        loadedMeter.Comments = dr.GetString(dr.GetOrdinal("Comments"));

                    //Load meter's notes
                    query = new StringBuilder("SELECT LookupId, LookupValue, LookupText, LookupTypeFk, LookupFk ");
                    query.Append("FROM tb_Lookups INNER JOIN tb_NotesXMeters ON tb_Lookups.LookupId = tb_NotesXMeters.NoteFk ");
                    query.Append("INNER JOIN tb_Meters ON tb_Meters.MeterId = tb_NotesXMeters.MeterFk ");
                    query.Append("WHERE tb_Meters.PoleFk = @PoleId ");
                    command = new SqlCeCommand(query.ToString(), con);
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, loadedMeter.Pole.Id));

                    loadedMeter.Notes = new List<ELookUp>();
                    SqlCeDataReader drNotes = command.ExecuteReader();

                    while (drNotes.Read())
                    {
                        ELookUp note = new ELookUp();

                        note.Id = drNotes.GetInt32(drNotes.GetOrdinal("LookupId"));
                        note.Type = LookUpType.MeterNote;

                        if (!drNotes.IsDBNull(drNotes.GetOrdinal("LookupFk")))
                        {
                            note.LookUp = new ELookUp();
                            note.LookUp.Id = drNotes.GetInt32(drNotes.GetOrdinal("LookupFk"));
                        }

                        note.Text = drNotes.GetString(drNotes.GetOrdinal("LookupText"));
                        note.Value = drNotes.GetInt32(drNotes.GetOrdinal("LookupValue"));

                        loadedMeter.Notes.Add(note);
                    }
                }

                return loadedMeter;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static EMeter Save (EMeter meter)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Pole INSERT -

                query.AppendLine("INSERT INTO tb_Meters (Latitude, Longitude, PoleFk, Number, ImageFileName, Comments) VALUES (");
                query.AppendLine("@Latitude,");
                query.AppendLine("@Longitude,");
                query.AppendLine("@PoleFk,");
                query.AppendLine("@Number,");
                query.AppendLine("@ImageFileName,");
                query.AppendLine("@Comments )");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                if (meter.Latitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, meter.Latitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (meter.Longitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, meter.Longitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, meter.Pole.Id));

                if (meter.Image != null)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@ImageFileName", 250, ParameterDirection.Input, false, meter.Image.FileName));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@ImageFileName", 250, ParameterDirection.Input, false, DBNull.Value));

                if (meter.Number != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, meter.Number));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, DBNull.Value));

                if (meter.Comments != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, meter.Comments));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));

                command.ExecuteNonQuery();

                //Gets the last inserted PoleId 
                query = new StringBuilder();
                query.AppendLine("SELECT MAX(MeterId) AS id FROM tb_Meters");
                
                command = new SqlCeCommand(query.ToString(), (SqlCeConnection)tx.Connection, tx);
                command.CommandType = CommandType.Text;
                Int32 lastId = (int)command.ExecuteScalar();

                #endregion

                #region - Update Image file name -

                if (meter.Image != null) 
                {
                    query = new StringBuilder();
                    query.AppendLine("UPDATE tb_Meters SET ImageFileName = @ImageFileName WHERE MeterId = @MeterId");

                    command = new SqlCeCommand(query.ToString(), (SqlCeConnection)tx.Connection, tx);
                    command.CommandType = CommandType.Text;

                    meter.Image.GenerateNewName(lastId.ToString());
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@ImageFileName", 250, ParameterDirection.Input, false, meter.Image.FileName));
                    command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, lastId));

                    command.ExecuteNonQuery();
                }

                #endregion

                #region - Phases INSERT -

                foreach (ELookUp note in meter.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_NotesXMeters (MeterFk, NoteFk) VALUES (");
                    query.AppendLine("@MeterFk, @NoteFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@MeterFk", ParameterDirection.Input, false, lastId));
                    command.Parameters.Add(Helper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, note.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                tx.Commit();

                meter.Id = lastId;

                return meter;
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static EMeter Update(EMeter meter)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Pole UPDATE -

                query.AppendLine("UPDATE tb_Meters SET ");
                query.AppendLine("Latitude = @Latitude,");
                query.AppendLine("Longitude = @Longitude,");
                query.AppendLine("PoleFk = @PoleFk,");
                query.AppendLine("Number = @Number,");
                query.AppendLine("ImageFileName = @ImageFileName,");
                query.AppendLine("Comments = @Comments ");
                query.AppendLine("WHERE MeterId = @MeterId");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                if (meter.Latitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, meter.Latitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (meter.Longitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, meter.Longitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, meter.Pole.Id));

                if (meter.Number != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, meter.Number));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, DBNull.Value));

                if (meter.Image != null)
                {
                    meter.Image.GenerateNewName(meter.Id.ToString());
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@ImageFileName", 250, ParameterDirection.Input, false, meter.Image.FileName));
                }
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@ImageFileName", 250, ParameterDirection.Input, false, DBNull.Value));

                if (meter.Comments != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, meter.Comments));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));

                command.ExecuteNonQuery();

                #endregion

                #region - Notes UPDATE -

                query = new StringBuilder("DELETE tb_NotesXMeters ");
                query.AppendLine("WHERE MeterFk = @MeterId ");

                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                command.ExecuteNonQuery();

                foreach (ELookUp note in meter.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_NotesXMeters (MeterFk, NoteFk) VALUES (");
                    query.AppendLine("@MeterFk, @NoteFk)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@MeterFk", ParameterDirection.Input, false, meter.Id));
                    command.Parameters.Add(Helper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, note.Value));

                    command.ExecuteNonQuery();
                }

                #endregion

                tx.Commit();

                return meter;
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static void Delete(EMeter meter)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Meter DELETE -

                query.AppendLine("DELETE tb_Meters ");
                query.AppendLine("WHERE MeterId = @MeterId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                command.ExecuteNonQuery();

                #endregion

                #region - Notes DELETE -

                foreach (ELookUp note in meter.Notes)
                {
                    query = new StringBuilder("DELETE tb_NotesXMeters ");
                    query.AppendLine("WHERE NoteFk = @NoteFk AND MeterFk = @MeterId");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, note.Id));
                    command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                    command.ExecuteNonQuery();
                }

                #endregion

                tx.Commit();
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static void Delete(List<EMeter> meters)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                foreach (EMeter meter in meters)
                {
                    #region - Meter DELETE -

                    query.AppendLine("DELETE tb_Meters ");
                    query.AppendLine("WHERE MeterId = @MeterId ");

                    SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;

                    command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                    command.ExecuteNonQuery();

                    #endregion

                    #region - Notes DELETE -

                    foreach (ELookUp note in meter.Notes)
                    {
                        query = new StringBuilder("DELETE tb_NotesXMeters ");
                        query.AppendLine("WHERE NoteFk = @NoteFk AND MeterFk = @MeterId");

                        command = new SqlCeCommand(query.ToString(), con, tx);
                        command.CommandType = CommandType.Text;
                        command.Parameters.Add(Helper.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, note.Id));
                        command.Parameters.Add(Helper.CreateIntParameter("@MeterId", ParameterDirection.Input, false, meter.Id));

                        command.ExecuteNonQuery();
                    }
                    #endregion
                }

                tx.Commit();
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static List<EMeter> GetAll(EPole pole)
        {
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();

                query.Append("SELECT MeterId, Latitude, Longitude, Number, ImageFileName, Comments ");
                query.Append("FROM tb_Meters ");
                query.Append("WHERE PoleFk = @PoleId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                SqlCeDataReader dr = command.ExecuteReader();
                List<EMeter> meters = new List<EMeter>();

                while (dr.Read())
                {
                    EMeter loadedMeter = new EMeter();

                    loadedMeter.Id = dr.GetInt32(dr.GetOrdinal("MeterId"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Latitude")))
                        loadedMeter.Latitude = dr.GetDouble(dr.GetOrdinal("Latitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Longitude")))
                        loadedMeter.Longitude = dr.GetDouble(dr.GetOrdinal("Longitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Number")))
                        loadedMeter.Number = dr.GetString(dr.GetOrdinal("Number"));

                    if (!dr.IsDBNull(dr.GetOrdinal("ImageFileName"))) {
                        loadedMeter.Image = new EImage(dr.GetString(dr.GetOrdinal("ImageFileName")));
                        loadedMeter.Image.ImageType = ImageType.Meter;
                    }

                    if (!dr.IsDBNull(dr.GetOrdinal("Comments")))
                        loadedMeter.Comments = dr.GetString(dr.GetOrdinal("Comments"));

                    //Load meter's images
                    query = new StringBuilder("SELECT LookupId, LookupValue, LookupText, LookupTypeFk, LookupFk ");
                    query.Append("FROM tb_Lookups INNER JOIN tb_NotesXMeters ON tb_Lookups.LookupId = tb_NotesXMeters.NoteFk ");
                    query.Append("INNER JOIN tb_Meters ON tb_Meters.MeterId = tb_NotesXMeters.MeterFk ");
                    query.Append("WHERE tb_Meters.PoleFk = @PoleId ");
                    command = new SqlCeCommand(query.ToString(), con);
                    command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                    loadedMeter.Notes = new List<ELookUp>();
                    SqlCeDataReader drNotes = command.ExecuteReader();

                    while (drNotes.Read())
                    {
                        ELookUp note = new ELookUp();

                        note.Id = drNotes.GetInt32(drNotes.GetOrdinal("LookupId"));
                        note.Type = LookUpType.MeterNote;

                        if (!drNotes.IsDBNull(drNotes.GetOrdinal("LookupFk")))
                        {
                            note.LookUp = new ELookUp();
                            note.LookUp.Id = drNotes.GetInt32(drNotes.GetOrdinal("LookupFk"));
                        }

                        note.Text = drNotes.GetString(drNotes.GetOrdinal("LookupText"));
                        note.Value = drNotes.GetInt32(drNotes.GetOrdinal("LookupValue"));

                        loadedMeter.Notes.Add(note);
                    }

                    meters.Add(loadedMeter);
                }

                return meters;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        #endregion
    }
}
